package com.vca.service.service.Impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriteConfig;
import cn.hutool.core.text.csv.CsvWriter;
import com.vca.common.constants.Constants;
import com.vca.common.utils.DateUtil;
import com.vca.service.service.ObsService;
import com.vca.service.service.SystemConfigService;
import com.vca.service.util.OBSUtils;
import jodd.util.StringUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class ObsServiceImpl implements ObsService {

    public static final Logger logger = LoggerFactory.getLogger(ObsServiceImpl.class);

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${vca.proxy.disable}")
    private Boolean disable;

    @Value("${vca.obs.name}")
    private String name;

    @Resource
    private SystemConfigService systemConfigService;

    public static final String selectOrder = "SELECT `id`, `order_id`, `uid`,`city`,`district`, `freight_price`, `total_num`, `total_price`, `total_postage`, `pay_price`, `pay_integral`, `integral_id`, `pay_postage`, `deduction_price`, `coupon_id`, `coupon_price`, `paid`, `pay_type`, `pay_time`, `create_time`, `status`, `agreed_return_time`, `pick_up_date`, `pick_up_start_time`, `pick_up_end_time`, `refund_status`, `refund_reason_wap_img`, `refund_reason_wap_explain`, `refund_reason_wap`, `refund_reason`, `refund_application_time`, `refund_reason_time`, `refund_price`, `delivery_name`, `delivery_type`, `delivery_time`, `return_delivery_time`, `master_waybill_no`, `gain_integral`, `use_integral`, `back_integral`, `mark`, `is_del`, `remark`, `cost`, `verify_code`, `clerk_id`, `is_channel`, `is_system_del`, `update_time`, `delivery_code`, `type`, `pro_total_price`, `before_pay_price`, `is_alter_price`, `delivery_sf_status`, `return_sf_status`,  `invoicing_status`, `user_type`, `apply_refund_type`, `closing_time`, `after_sales_deadline` FROM vca_order";

    public static final String selectOrderInfo = "SELECT `id`, `main_id`, `type`, `product_id`, `info`, `create_time`, `update_time`, `order_no`, `mer_order_no`, `name`, `attr_value_id`, `image`, `sku`, `price`, `pay_num`, `weight`, `volume`, `language`, `taoke_about_course_id`, `scheduling_id`, `scheduling_date`, `scheduling_start_time`, `scheduling_end_time`, `pay_price`, `refund_status`, `refund_reason_wap_img`, `refund_reason_wap_explain`, `refund_reason_wap`, `refund_reason`, `refund_reason_time`, `refund_application_time`, `refund_price`, `buyer`, `lecturer`, `is_get`, `write_off_clerk_id`, `write_off_time`, `status`, `apply_refund_type`, `is_tip` FROM vca_order_info";

    public static final String selectUser = "SELECT `uid`,`union_id`, `create_time`, `last_login_time`,`user_register_type`, `status`, `mark`, `now_money`, `integral`, `user_type`,  `adminid`, `login_type`, `update_time`, `clean_time`, `birthday`, `sex` FROM vca_user";

    public static final String selectUserToken = "SELECT `id`,`uid`,`token` FROM vca_user_token";

    public static final String tableNames = "vca_about,vca_cart,vca_category,vca_course,vca_course_about,vca_course_info,vca_course_package," +
            "vca_course_package_relation,vca_course_scheduling,vca_exhibition,vca_exhibition_info," +
            "vca_exhibition_scheduling,vca_line_up,vca_order,vca_order_info,vca_product,vca_product_attr,vca_product_attr_result," +
            "vca_product_attr_value,vca_product_info,vca_product_rule,vca_talk,vca_talk_info,vca_talk_scheduling,vca_user,vca_user_card," +
            "vca_user_card_record,vca_user_collection,vca_user_token";


    /**
     * @Description:OBS同步数据
     * @author:chenbing
     * @date 2023/6/12 18:28
     */
    @Override
    public void sync() {
        exportDataToCsvByTables();
        String obsAk = systemConfigService.getValueByKey("obs_ak");
        String obsSk = systemConfigService.getValueByKey("obs_sk");
        String obsEndpoint = systemConfigService.getValueByKey("obs_endpoint");
        String obsBucket = systemConfigService.getValueByKey("obs_bucket");
        String[] tableNameList = tableNames.split(",");
        for (String tableName : tableNameList) {
            String path = "/localfile/obs/" + tableName + ".csv";
            File file = new File(path);
            if (file.exists()) {
                if (!disable) {
                    System.getProperties().clear();
                }
                String table = tableName.replace("vca_", "");
                logger.error("nameDir===>{}", name);
                OBSUtils.uploadObject(name, obsEndpoint, obsAk, obsSk, obsBucket, file, table);
            }
        }
        logger.info("导出成功");
    }


    public void exportDataToCsvByTables() {
        try {
            String[] tableNameList = tableNames.split(",");
            // 连接数据库
            Connection connection = DriverManager.getConnection(url, username, password);
            for (String tableName : tableNameList) {
                String beginDate = DateUtil.dateToStr(cn.hutool.core.date.DateUtil.yesterday(), Constants.DATE_FORMAT_START);
                String endDate = DateUtil.dateToStr(cn.hutool.core.date.DateUtil.yesterday(), Constants.DATE_FORMAT_END);
                String query = "SELECT * FROM `" + tableName + "`";
                if ("vca_order".equals(tableName)) {
                    query = selectOrder;
                }
                if ("vca_order_info".equals(tableName)) {
                    query = selectOrderInfo;
                }
                if ("vca_user".equals(tableName)) {
                    query = selectUser;
                }
                if ("vca_user_token".equals(tableName)) {
                    query = selectUserToken;
                }
                /*if ("vca_order".equals(tableName) || "vca_order_info".equals(tableName)) {
                    query += " where `update_time` BETWEEN '" + beginDate + "'AND '" + endDate + "'";
                }*/
                exportTableDataToCSV(tableName, connection, query);
            }
            connection.close();
            System.out.println("数据导出完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportTableDataToCSV(String tableName, Connection connection, String query) throws SQLException, IOException {
        System.out.println(query);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(query);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        CsvWriteConfig config = new CsvWriteConfig();
        config.setFieldSeparator('|');
        config.setAlwaysDelimitText(true);

        Map<String,String> tableFiledAlias = new LinkedHashMap<>();

        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            tableFiledAlias.put(columnName,columnName);
        }
        config.setHeaderAlias(tableFiledAlias);

        FileWriter fileWriter = new FileWriter("/localfile/obs/" + tableName + ".csv");

        CsvWriter writer = CsvUtil.getWriter(fileWriter, config);


      /*  // 创建CSV文件
        File csvFile = new File("/localfile/obs/" + tableName + ".csv");
        // 创建写入器并指定UTF-8编码
        FileOutputStream fileOutputStream = new FileOutputStream(csvFile);
        //追加BOM标识
        Writer writer = new BufferedWriter(new OutputStreamWriter(fileOutputStream, StandardCharsets.UTF_8));
        List<String> tableFileds = new ArrayList<>();
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            tableFileds.add(columnName);
        }
        String tableHead = StringUtil.join(tableFileds.toArray(), ",") + "\n";
        System.out.println("tableHead = " + tableHead);
        // 写入csv文件头部
        writer.write(tableHead);*/

        List<String> tableFileds = new ArrayList<>();
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            tableFileds.add(columnName);
        }

        ArrayList<Object> writerList = CollUtil.newArrayList();
        writerList.add(tableFileds);
        while (resultSet.next()) {
            String row = "";
            Integer num = 0;

            ArrayList<String> list = new ArrayList<>();
            for (String tableFiled : tableFileds) {
                num++;
                String value = resultSet.getString(tableFiled);
                /*if(StringUtil.isNotBlank(row)){
                    row = row.replace(",","#");
                }*/
                list.add(value);
               /* if (StringUtil.isBlank(value)) {
                    value = "\t";
                }
                if (num == tableFileds.size()) {
                    row += "\"" + value + "\"" + "\n";
                } else {
                    row += "\"" + value + "\"" + ",";
                }*/
            }
            writerList.add(list);

//            System.out.println("row = " + row);
//            writer.write(row);

        }
            writer.write(writerList);
        // 关闭资源
        writer.flush();
        writer.close();
        resultSet.close();
        statement.close();
    }


    private void writeResultSetToSheet(ResultSet resultSet, Sheet sheet) throws Exception {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 写入表头
        Row headerRow = sheet.createRow(0);
        for (int i = 1; i <= columnCount; i++) {
            Cell cell = headerRow.createCell(i - 1);
            cell.setCellValue(metaData.getColumnName(i));
        }

        // 写入数据
        int rowIndex = 1;
        while (resultSet.next()) {
            Row dataRow = sheet.createRow(rowIndex);
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = dataRow.createCell(i - 1);
                cell.setCellValue(resultSet.getString(i));
            }
            rowIndex++;
        }
    }
}
